﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using UniversityStudentManagemnetSystem.DAO.DAL;

namespace UniversityStudentManagemnetSystem.DAO.Gateway
{
    public class OfficerGateway:Gateway
    {
        Officer aOfficer=new Officer();
        public bool SaveOfficer(Officer aOfficer)
        {
            try
            {
                Connection.Open();
                string insertQuery = string.Format("insert into t_Officer values (@Name,@DesignationId,@FatherName,@MotherName,@PresentAddress,@PermanentAddress,@NationalId,@EmailAddress,@ContactNumber,@PastExperience,@DateOfBirth,@JoiningDate,@FileLocation)");
                Command.CommandText = insertQuery;
                Command.Parameters.Add("@Name", SqlDbType.VarChar);
                Command.Parameters["@Name"].Value = aOfficer.Name;

                Command.Parameters.Add("@DesignationId", SqlDbType.Int);
                Command.Parameters["@DesignationId"].Value = aOfficer.Designation.OfficerDesignation;

                Command.Parameters.Add("@FatherName", SqlDbType.VarChar);
                Command.Parameters["@FatherName"].Value = aOfficer.FatherName;

                Command.Parameters.Add("@MotherName", SqlDbType.VarChar);
                Command.Parameters["@MotherName"].Value = aOfficer.MotherName;

                Command.Parameters.Add("@PresentAddress", SqlDbType.VarChar);
                Command.Parameters["@PresentAddress"].Value = aOfficer.PresentAaddres;

                Command.Parameters.Add("@PermanentAddress", SqlDbType.VarChar);
                Command.Parameters["@PermanentAddress"].Value = aOfficer.PermanentAaddres;

                Command.Parameters.Add("@NationalId", SqlDbType.VarChar);
                Command.Parameters["@NationalId"].Value = aOfficer.NationalIdNumber;

                Command.Parameters.Add("@EmailAddress", SqlDbType.VarChar);
                Command.Parameters["@EmailAddress"].Value = aOfficer.Email;

                Command.Parameters.Add("@ContactNumber", SqlDbType.VarChar);
                Command.Parameters["@ContactNumber"].Value = aOfficer.ContactNumber;

                Command.Parameters.Add("@PastExperience", SqlDbType.VarChar);
                Command.Parameters["@PastExperience"].Value = aOfficer.PastExperience;

                Command.Parameters.Add("@DateOfBirth", SqlDbType.DateTime);
                Command.Parameters["@DateOfBirth"].Value = aOfficer.DateOfBirth;

                Command.Parameters.Add("@JoiningDate", SqlDbType.DateTime);
                Command.Parameters["@JoiningDate"].Value = aOfficer.JoiningDate;

                Command.Parameters.Add("@FileLocation", SqlDbType.VarChar);
                Command.Parameters["@FileLocation"].Value = aOfficer.FileLocation;

                Command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception("System Error!", exception);
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return true;
        }

        public DataTable GetAllOfficer()
        {
            try
            {
                string selectQuery =
                    string.Format("select * from t_Officer");
                Command.CommandText = selectQuery;
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectQuery, GetConnectionString());
                DataSet dataSet = new DataSet();
                sqlDataAdapter.Fill(dataSet, "OfficerTable");
                DataTable myDataTable = dataSet.Tables["OfficerTable"];
                nbrOfTableRows = myDataTable.Rows.Count;
                return myDataTable;

            }
            catch (Exception exception)
            {


                throw new Exception("System Error!", exception);
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
           
        }

        public DataTable GetAllOfficerFor(int selectedDesignation)
        {
            try
            {
                string selectQuery =
                    string.Format("select * from t_Officer where DesignationId='" + selectedDesignation + "'");
                Command.CommandText = selectQuery;
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectQuery, GetConnectionString());
                DataSet dataSet = new DataSet();
                sqlDataAdapter.Fill(dataSet, "OfficerTable");
                DataTable myDataTable = dataSet.Tables["OfficerTable"];
                nbrOfTableRows = myDataTable.Rows.Count;
                return myDataTable;

            }
            catch (Exception exception)
            {


                throw new Exception("System Error!", exception);
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
        }

        public Officer GetOfficerFor(string searchText)
        {

            try
            {
                Connection.Open();
                string selectQuery =
                    //string.Format("Select * from t_Officer where NationalIdNumber='" + searchText + "' or Email='" +
                    //              searchText + "' or ContactNumber='" + searchText + "'");
                    string.Format(
                        "SELECT  od.Name, o.* FROM dbo.t_Officer as o INNER JOIN  dbo.t_OfficerDesignation as od ON o.Id = od.Id where NationalIdNumber='" +
                        searchText + "' or Email='" + searchText + "' or ContactNumber='" + searchText + "'");
                Command.CommandText = selectQuery;
                SqlDataReader reader = Command.ExecuteReader();
                while (reader.Read())
                {
                    aOfficer.Name = reader["Name"].ToString();
                    aOfficer.Designation.DesignationId = Convert.ToInt32(reader["DesignationId"]);
                    aOfficer.Designation.DesignationName = reader["Name"].ToString();
                    aOfficer.FatherName = reader["FatherName"].ToString();
                    aOfficer.MotherName = reader["MotherName"].ToString();
                    aOfficer.PresentAaddres = reader["PresentAddress"].ToString();
                    aOfficer.PermanentAaddres = reader["PermanentAddress"].ToString();
                    aOfficer.NationalIdNumber = reader["NationalIdNumber"].ToString();
                    aOfficer.Email = reader["Email"].ToString();
                    aOfficer.ContactNumber = reader["ContactNumber"].ToString();
                    aOfficer.PastExperience = reader["PastExperience"].ToString();
                    aOfficer.DateOfBirth = Convert.ToDateTime(reader["DateOfBirth"]);
                    aOfficer.JoiningDate = Convert.ToDateTime(reader["JoiningDate"]);
                }
            }
            catch (Exception exception)
            {

                throw new Exception("Sorry!System Error!", exception);
            }

            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return aOfficer;
        }

        public int GetPreviousOfficerId()
        {
            int id = 0;
            try
            {
                Connection.Open();
                string selectQuery = string.Format("select Id from t_Officer");
                Command.CommandText = selectQuery;
                SqlDataReader sqlDataReader = Command.ExecuteReader();
                while (sqlDataReader.Read())
                {
                    id = Convert.ToInt32(sqlDataReader["Id"]);
                }
            }
            catch (Exception exception)
            {
                throw new Exception("System Error!", exception);
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return id;
        }

        public bool HasThisEmail(string email)
        {
            bool hasRows = false;
            try
            {
                Connection.Open();
                string selectQuery = string.Format("select * from t_Officer where Email = @email");
                Command.CommandText = selectQuery;
                Command.Parameters.Clear();
                Command.Parameters.Add("@email", SqlDbType.VarChar);
                Command.Parameters["@email"].Value = email;
                SqlDataReader sqlDataReader = Command.ExecuteReader();
                if (sqlDataReader != null)
                {
                    hasRows = sqlDataReader.HasRows;
                }
            }
            catch (Exception exception)
            {
                throw new Exception("System Error!", exception);
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return hasRows;
        }

        public bool HasThisNationalId(string nationalIdNumber)
        {

            bool hasRows = false;
            try
            {
                Connection.Open();
                string selectQuery = string.Format("select * from t_Officer where NationalIdNumber = @NationalIdNumber");
                Command.CommandText = selectQuery;
                Command.Parameters.Clear();
                Command.Parameters.Add("@NationalIdNumber", SqlDbType.VarChar);
                Command.Parameters["@NationalIdNumber"].Value = nationalIdNumber;
                SqlDataReader sqlDataReader = Command.ExecuteReader();
                if (sqlDataReader != null)
                {
                    hasRows = sqlDataReader.HasRows;
                }
            }
            catch (Exception exception)
            {
                throw new Exception("System Error!", exception);
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return hasRows;
        }
    }
}